package webapp;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;

import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import com.mysql.jdbc.PreparedStatement;

/**
 * Servlet implementation class Sale
 */
public class Sale extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public Sale() {
        super();
        // TODO Auto-generated constructor stub
        
    }

	/**
	 * @see Servlet#init(ServletConfig)
	 */
	public void init(ServletConfig config) throws ServletException {
		// TODO Auto-generated method stub
	}

	/**
	 * @see HttpServlet#service(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void service(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		System.out.println("We are in Service method of Sale");
		PrintWriter out=response.getWriter(); 
		
		HttpSession session=request.getSession(false); 
		String un = (String) session.getAttribute("name");
		
        if(un!=null){
		
		String rm=request.getParameter("RiceMill");
		String ag=request.getParameter("Agency");
		String si=request.getParameter("StockItems");
		String pd=request.getParameter("PurchaseDate");
		String nw=request.getParameter("Weight");
		String pr=request.getParameter("Price");
		
		String msg=" ";
		
		String url = "jdbc:mysql://localhost:3306/";
		 String dbName = "webappschema";
		 String driver = "com.mysql.jdbc.Driver";
		 String dbusername = "root";
		 String dbpassword = "webapp";
		 
		 
		 if(rm.equals("Selection") || ag.equals("Selection") || 
			        si.equals("Selection") || pd.equals("") || nw.equals("")|| pr.equals("") ) 
		 {
		        msg = "Please insert valid data";
		        response.setContentType("text/html");
		     
		        out.println("<font size = '6' color=red>" + msg + "</font>");
		      
		        
		    }
		 else
		    {
		    	
		   
	 try {Class.forName(driver).newInstance();
	 Connection con = DriverManager.getConnection(url+dbName,dbusername,dbpassword);
	 String strQuery = ("INSERT INTO sale (Date, Weight, Price, Agency_Agencyid, RiceMill_RiceMillid, Stock_Stockid) Select ?,?,?, Agencyid, RiceMillid, Stockid From agency, ricemill, stock Where agency.AgencyName = '" + ag + "' and ricemill.RiceMillName = '" + rm + "' and stock.StockName = '" + si + "'" );
	 PreparedStatement ps = (PreparedStatement) con.prepareStatement(strQuery);
	 
	 ps.setString(1, pd);
     ps.setString(2, nw);
     ps.setString(3, pr);
     
    
     
     int i=ps.executeUpdate();

     if(i>0)
     {
    	 out.println("<script type=\"text/javascript\">");
 		out.println("alert('Record successfully inserted');");
 		out.println("location='Sale.jsp';");
 		out.println("</script>");
    	 //msg = "Record successfully inserted";
        
         
         }
     ps.close(); 
     con.close();
 
	 } catch (Exception e) {
	   e.printStackTrace();
	 }

	 



response.setContentType("text/html");

out.println("<font size = '6' color=red>" + msg + "</font>");

	    }
        }
		 else{  
				out.print("Please login first");    
	            request.getRequestDispatcher("index.jsp").include(request, response);  
	        } 
	        out.close();
		
	}

}
